package tum0r.sql;

import tum0r.utils.PageModel;
import tum0r.utils.StringExtension;
import tum0r.utils.TypeExtension;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 工程: SQLTool<br>
 * 包: tum0r.sql<br>
 * 创建者: tum0r<br>
 * 创建时间: 2020/10/2 19:19<br>
 * <br>
 */
public abstract class SQL {
    protected Connection connection;
    protected PreparedStatement statement;
    protected ResultSet resultSet;

    public void init(String driverName) throws ClassNotFoundException {
        Class.forName(driverName);
    }

    public abstract boolean open(String host, int port, String databaseName, String username, String password) throws SQLException;

    public boolean open(int port, String databaseName, String username, String password) throws SQLException {
        return open("127.0.0.1", port, databaseName, username, password);
    }

    public boolean open(int port, String username, String password) throws SQLException {
        return open("127.0.0.1", port, "", username, password);
    }

    public abstract boolean open(String username, String password) throws SQLException;

    public abstract boolean open(String databaseName, String username, String password) throws SQLException;

    public boolean open(int port) throws SQLException {
        return open("127.0.0.1", port, "", "", "");
    }

    public abstract boolean open() throws SQLException;

    public boolean open(int port, String databaseName) throws SQLException {
        return open("127.0.0.1", port, databaseName, "", "");
    }

    public boolean open(String host, int port) throws SQLException {
        return open(host, port, "", "", "");
    }

    public boolean open(String host, int port, String username, String password) throws SQLException {
        return open(host, port, "", username, password);
    }

    public boolean isOpen() {
        boolean result;
        try {
            result = connection != null && !connection.isClosed();
        } catch (SQLException ignored) {
            result = false;
        }
        return result;
    }

    private boolean setParameters(String command, Object... parameters) throws Exception {
        boolean result = false;
        if (connection != null && StringExtension.isNotNullOrOnlyWhiteSpace(command)) {
            ArrayList<Object> parametersTemp = new ArrayList<>();
            boolean flag = true;
            if (parameters != null) {
                String regex = "(@[p|P](\\d+))";
                Pattern pattern = Pattern.compile(regex);
                Matcher matcher = pattern.matcher(command);
                while (matcher.find()) {
                    int index = Integer.parseInt(matcher.group(2));
                    if (index < parameters.length) {
                        parametersTemp.add(parameters[index]);
                    } else {
                        flag = false;
                        break;
                    }
                }
                command = command.replaceAll(regex, "?");
            }
            if (flag) {
                if (parametersTemp.size() != 0) {
                    parameters = parametersTemp.toArray();
                }
                statement = connection.prepareStatement(command);
                if (parameters != null) {
                    for (int i = 0; i < parameters.length; i++) {
                        statement.setObject(i + 1, parameters[i]);
                    }
                }
                result = true;
            }
        }
        return result;
    }

    private <T> ArrayList<T> selectObjectsBase(Class<T> c) throws Exception {
        ArrayList<T> result = new ArrayList<>();
        if (resultSet != null && !resultSet.isClosed()) {
            ArrayList<String> columnName = new ArrayList<>();
            ResultSetMetaData metaData = resultSet.getMetaData();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                columnName.add(metaData.getColumnName(i));
            }

            while (resultSet.next()) {
                T item = c.getConstructor().newInstance();
                Field[] files = c.getFields();
                if (files.length != 0) {
                    for (Field field : files) {
                        if (columnName.contains(field.getName())) {
                            field.setAccessible(true);
                            field.set(item, resultSet.getObject(columnName.lastIndexOf(field.getName()) + 1, field.getType()));
                            field.setAccessible(false);
                        }
                    }
                }
                result.add(item);
            }
            close(resultSet);
        }
        return result;
    }

    /**
     * @deprecated This method will cause SQL injection, replaced by <code>selectObjects(String command, Class<T> c, Object... parameters)</code>
     */
    @Deprecated
    public <T> ArrayList<T> selectObjectsUnsafe(String command, Class<T> c) throws Exception {
        ArrayList<T> result;
        if (c != null && !TypeExtension.isPrimitive(c)) {
            Statement statement = connection.createStatement();
            resultSet = statement.executeQuery(command);
            result = selectObjectsBase(c);
            close(statement);
        } else {
            result = new ArrayList<>();
        }
        return result;
    }

    public <T> ArrayList<T> selectObjects(String command, Class<T> c, Object... parameters) throws Exception {
        ArrayList<T> result;
        if (c != null && !TypeExtension.isPrimitive(c) && setParameters(command, parameters)) {
            resultSet = statement.executeQuery();
            result = selectObjectsBase(c);
            close(statement);
        } else {
            result = new ArrayList<>();
        }
        return result;
    }

    /**
     * @deprecated This method will cause SQL injection, replaced by <code>selectObject(String command, Class<T> c, Object... parameters)</code>
     */
    @Deprecated
    public <T> T selectObjectUnsafe(String command, Class<T> c) throws Exception {
        ArrayList<T> temp = selectObjectsUnsafe(command, c);
        return temp.size() > 0 ? temp.get(0) : null;
    }

    public <T> T selectObject(String command, Class<T> c, Object... parameters) throws Exception {
        ArrayList<T> temp = selectObjects(command, c, parameters);
        return temp.size() > 0 ? temp.get(0) : null;
    }

    private <T> ArrayList<T> selectValuesBase(Class<T> c) throws Exception {
        ArrayList<T> result = new ArrayList<>();
        if (resultSet != null && !resultSet.isClosed()) {
            if (resultSet.getMetaData().getColumnCount() >= 1) {
                while (resultSet.next()) {
                    result.add(resultSet.getObject(1, c));
                }
            }
            close(resultSet);
        }
        return result;
    }

    /**
     * @deprecated This method will cause SQL injection, replaced by <code>selectValues(String command, Class<T> c, Object... parameters)</code>
     */
    @Deprecated
    public <T> ArrayList<T> selectValuesUnsafe(String command, Class<T> c) throws Exception {
        ArrayList<T> result;
        if (c != null && TypeExtension.isPrimitive(c)) {
            Statement statement = connection.createStatement();
            resultSet = statement.executeQuery(command);
            result = selectValuesBase(c);
        } else {
            result = new ArrayList<>();
        }
        return result;
    }

    public <T> ArrayList<T> selectValues(String command, Class<T> c, Object... parameters) throws Exception {
        ArrayList<T> result;
        if (c != null && TypeExtension.isPrimitive(c) && setParameters(command, parameters)) {
            resultSet = statement.executeQuery();
            result = selectValuesBase(c);
        } else {
            result = new ArrayList<>();
        }
        return result;
    }

    /**
     * @deprecated This method will cause SQL injection, replaced by <code>selectValue(String command, Class<T> c, Object... parameters)</code>
     */
    @Deprecated
    public <T> T selectValueUnsafe(String command, Class<T> c) throws Exception {
        ArrayList<T> temp = selectValuesUnsafe(command, c);
        return temp.size() > 0 ? temp.get(0) : null;
    }

    public <T> T selectValue(String command, Class<T> c, Object... parameters) throws Exception {
        ArrayList<T> temp = selectValues(command, c, parameters);
        return temp.size() > 0 ? temp.get(0) : null;
    }

    /**
     * @deprecated This method will cause SQL injection, replaced by <code>selectPage(int index, int pageSize, Class<T> c, String command, Object... parameters)</code>
     */
    @Deprecated
    public <T> PageModel<T> selectPageUnsafe(int index, int pageSize, Class<T> c, String command) throws Exception {
        PageModel<T> result = new PageModel<>();
        result.index = index;
        result.pageSize = pageSize;
        command = command == null ? "" : command;
        while (command.endsWith(";")) {
            command = command.substring(0, command.length() - 1);
        }
        result.count = selectValueUnsafe("SELECT COUNT(1) FROM (" + command + ") AS t", int.class);
        result.calc();
        if (index < result.allPage) {
            command = command + " LIMIT " + (index * pageSize) + ", " + pageSize;
            if (!TypeExtension.isPrimitive(c)) {
                result.model = selectObjectsUnsafe(command, c);
            } else {
                result.model = selectValues(command, c);
            }
        }
        return result;
    }

    public <T> PageModel<T> selectPage(int index, int pageSize, Class<T> c, String command, Object... parameters) throws Exception {
        PageModel<T> result = new PageModel<>();
        result.index = index;
        result.pageSize = pageSize;
        command = command == null ? "" : command;
        while (command.endsWith(";")) {
            command = command.substring(0, command.length() - 1);
        }
        result.count = selectValue("SELECT COUNT(1) FROM (" + command + ") AS t", int.class, parameters);
        result.calc();
        if (index < result.allPage) {
            command = command + " LIMIT " + (index * pageSize) + ", " + pageSize;
            if (!TypeExtension.isPrimitive(c)) {
                result.model.addAll(selectObjects(command, c, parameters));
            } else {
                result.model.addAll(selectValues(command, c, parameters));
            }
        }
        return result;
    }

    /**
     * @deprecated This method will cause SQL injection, replaced by <code>selectPage(int index, Class<T> c, String command, Object... parameters)</code>
     */
    @Deprecated
    public <T> PageModel<T> selectPageUnsafe(int index, Class<T> c, String command) throws Exception {
        return selectPageUnsafe(index, 15, c, command);
    }

    public <T> PageModel<T> selectPage(int index, Class<T> c, String command, Object... parameters) throws Exception {
        return selectPage(index, 15, c, command, parameters);
    }

    public int update(String command, Object... parameters) throws Exception {
        int result = 0;
        if (StringExtension.isNotNullOrOnlyWhiteSpace(command)) {
            String[] commands = command.split(";");
            for (String sql : commands) {
                if (setParameters(sql, parameters)) {
                    result += statement.executeUpdate();
                }
            }
        }
        return result;
    }

    public int updateMultiple(String command, Object... parameters) throws Exception {
        int result = 0;
        if (StringExtension.isNotNullOrOnlyWhiteSpace(command)) {
            setParameters(command, parameters);
            result = statement.executeUpdate();
        }
        return result;
    }

    public boolean initDatabase(String command, Object... parameters) {
        boolean result = false;
        try {
            result = update(command, parameters) >= 0;
        } catch (Exception ignored) {
        }
        return result;
    }

    private boolean close(AutoCloseable... parameters) throws Exception {
        boolean result = false;
        if (parameters != null) {
            for (AutoCloseable autoCloseable : parameters) {
                if (autoCloseable != null) {
                    autoCloseable.close();
                    autoCloseable = null;
                    result = true;
                }
            }
            System.gc();
        }
        return result;
    }

    public boolean close() throws Exception {
        return close(resultSet, statement, connection);
    }
}
